package com.aaa.yq127.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JDBCUtil {
    static String Driver="com.mysql.jdbc.Driver";
    static String url="jdbc:mysql://localhost:3306/fuxi?useUnicode=true&characterEncoding=utf8&useSSl=false";
    static String user="root";
    static String password="";

    static {
        try {
            Class.forName(Driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,user,password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    public static List<List> queryList(String sql,Object... obj){
        Connection conn = getConn();
        PreparedStatement ps = null;
        ResultSet set = null;
        List<List> biglist = null;
        try {
            ps = conn.prepareStatement(sql);
            if (obj!=null && obj.length!=0){
                for (int i = 1; i <= obj.length; i++) {
                    ps.setObject(i,obj[i-1]);
                }
            }
            set = ps.executeQuery();
            biglist = new ArrayList<>();
            while (set.next()){
                List<Object> list = new ArrayList<>();
                for (int i = 1; i <= set.getMetaData().getColumnCount(); i++) {
                    Object o = set.getObject(i);
                    list.add(o);
                }
                biglist.add(list);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(set,ps,conn);
        }
        return biglist;
    }

    public static List<Map> queryMap(String sql, Object... obj){
        Connection conn = getConn();
        ResultSet set = null;
        PreparedStatement ps = null;
        List<Map> biglist = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            if (obj != null&&obj.length != 0){
                for (int i = 1; i <= obj.length; i++) {
                    ps.setObject(i,obj[i-1]);
                }
            }
            set = ps.executeQuery();
            while (set.next()){
                Map<String,Object> map = new HashMap<>();
                for (int i = 1; i <= set.getMetaData().getColumnCount(); i++) {
                    map.put(set.getMetaData().getColumnName(i),set.getObject(i));
                }
                biglist.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(set,ps,conn);
        }
        return biglist;
    }

    public static int update(String sql,Object... obj){
        Connection conn = getConn();
        ResultSet set = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            if (obj.length>0){
                for (int i = 1; i <= obj.length; i++) {
                    ps.setObject(i,obj[i-1]);
                }
            }
            int i = ps.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(set,ps,conn);
        }
        return -1;
    }

    public static int insertkey(String sql,Object... obj){
        Connection conn = getConn();
        ResultSet set = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            if (obj.length>0){
                for (int i = 1; i <= obj.length; i++) {
                    ps.setObject(i,obj[i-1]);
                }
            }
            ps.executeUpdate();
            set = ps.getGeneratedKeys();
            if (set.next()){
                int i = set.getInt(1);
                return i;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(set,ps,conn);
        }
        return -1;
    }

    public static void closeAll(ResultSet set, PreparedStatement ps,Connection conn){
        if (set != null){
            try {
                set.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn !=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


}
